import pymysql

class DBUtil(object):
    def __init__(self):
        #  建立connect
        self.conn = pymysql.connect(
            host="localhost", user="root", password="1161105403",
            database="python", port=3306,
            charset='utf8'
        )
    #批量插入
    def batch_insert(self,records):
        # insert_sql = "insert into rank(id,school,province,score,index_score) values(%s,%s,%s,%s,%s)"
        insert_sql = "insert into xk(id,novel_name,book_type,author,end_time,intro,create_by) values(%s,%s,%s,%s,%s,%s,%s)"
        record_list=[]
        for item in records:
            record_list.append((item.get('id'),item.get('novel_name'),item.get('book_type'),
                                item.get('author'),item.get('end_time'),item.get('intro'),item.get('create_by')))

        try:
            #  获取cursor
            cursor = self.conn.cursor()
            #  执行SQL
            # cursor.execute(insert_sql, (1, "清华大学", "北京", 100, 99.88))
            # records = [(1, "清华大学", "北京", 100, 99.88), (2, "北京大学", "北京", 100, 99.88)]
            cursor.executemany(insert_sql, record_list)

            print(cursor.rowcount)
            self.conn.commit()

        except Exception as e:
            print(e)
            self.conn.rollback()
        finally:
            cursor.close()
            self.conn.close()


    #查询所有
    def find_all(self):
        #  执行SQL
        select_sql = "select * from xg"
        try:
            #  获取cursor
            cursor = self.conn.cursor()
            cursor.execute(select_sql)
            rest = cursor.fetchall()
            colums = [k[0] for k in cursor.description]
            records = []
            for item in rest:
                record = dict(zip([k[0] for k in cursor.description], item))
                records.append(record)
            # print(cursor.rowcount)
            return records

        except Exception as e:
            print(e)
        finally:
            cursor.close()
            self.conn.close()
